<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Model untuk Penelitian
 * 
 * @author artcak
 */
class penelitian_model extends CI_model {
   

    
    function __construct() {
        parent::__construct();
        $this->load->database();
    }
    
    /* Membuat Penelitian Baru */
    public function insert_penelitian($skim_id, $ketuaid, $judul, $abstraksi,$biaya1,$biaya2,$biaya3,$luaran)
    {
//        $this->db->where('SKIM_ID', $skim_id);
//        $this->db->where('SKIM_AKTIF',1);
//        $tmp1 = $this->db->get('skim');
//        if($tmp1->num_rows() <= 0)
//        {
//            return FALSE;
//        }
//
//        $this->db->where('peneliti_id', $ketuaid);
//        $tmp2 = $this->db->get('peneliti');
//        if($tmp2->num_rows() <= 0)
//        {
//            return FALSE;
//        }
//        
//        $this->db->where('peneliti_id', $ketuaid);
//        //Sebagai Ketua
//        $this->db->where('anggota_sebagai', 1);
//        $tmp2 = $this->db->get('anggota');
//        
//        if($tmp2->num_rows() > 0)
//        {
//            $mboh = $tmp2->row();
//            $this->db->where('penelitian_id',$mboh->PENELITIAN_ID);
//            $row = $this->db->get('penelitian')->result();
//            
//            if($row[0]->PENELITIAN_TANGGAL_SELESAI == NULL)
//            {
//                return false;
//            }
//            else if(strtotime($row[0]->PENELITIAN_TANGGAL_SELESAI) > now())
//            {
//                return false;
//            }
//        }
//        
        $STATUS_DAFTAR = 1;
        $data =  array(
            // Default Statusnya adalah MENDAFTAR
          'STATUSPEN_ID' => $STATUS_DAFTAR,
          'SKIM_ID' => $skim_id,
          'ADMIN_ID' => -1,
          'PENELITIAN_JUDUL' => $judul,
          'PENELITIAN_ABSTRAKSI' => $abstraksi,
          'PENELITIAN_BIAYA_THN1' => $biaya1,
          'PENELITIAN_BIAYA_THN2' => $biaya2,
          'PENELITIAN_BIAYA_THN3' => $biaya3,
          'PENELITIAN_DANA_PENGAJUAN' => $biaya1+$biaya2+$biaya3,
          'PENELITIAN_JUM_DANA_DISETUJUI' => 0,
          'PENELITIAN_TANGGAL_DAFTAR' => date("Y-m-d H:i:s"),
          'PENELITIAN_STATUS_PROPOSAL_DAFTAR' => 0,
          'PENELITIAN_STATUS_PROPOSAL_REVISI' => 0,
          'PENELITIAN_STATUS_LAPORAN_KEMAJUAN' => 0,
          'PENELITIAN_STATUS_LAPORAN_AKHIR' => 0,
          'PENELITIAN_STATUS_LAPORAN_PJ' => 0,
          'PENELITIAN_STATUS_SURAT_PERJANJIAN' => 0,
          'PENELITIAN_STATUS_SK_SELESAI' => 0,
          'PENELITIAN_AKTIF' => 1
        );
        $this->db->insert('penelitian',$data);
    }
    
    /* Edit Penelitian */
    public function update_penelitian($penelitian_id,$skim_id, $judul, $abstraksi, 
            $biaya_thn1, $biaya_thn2, $biaya_thn3, $dana_pengajuan)
    {
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $this->db->where('PENELITIAN_AKTIF',1);
        $tmp1 = $this->db->get('penelitian');
        if($tmp1->num_rows() <= 0)
        {
            return FALSE;
        }
        
        $this->db->where('SKIM_ID', $skim_id);
        $this->db->where('SKIM_AKTIF',1);
        $tmp2 = $this->db->get('skim');
        if($tmp2->num_rows() <= 0)
        {
            return FALSE;
        }        
        $data = array(
            'SKIM_ID' => $skim_id,
            'PENELITIAN_JUDUL' => $judul,
            'PENELITIAN_ABSTRAKSI' => $abstraksi,
            'PENELITIAN_BIAYA_THN1' => $biaya_thn1,
            'PENELITIAN_BIAYA_THN2' => $biaya_thn2,
            'PENELITIAN_BIAYA_THN2' => $biaya_thn3,
            'PENELITIAN_DANA_PENGAJUAN' => $dana_pengajuan
        );        
        $this->db->where('PENELITIAN_ID',$penelitian_id);
        $result = $this->db->update('penelitian', $data);
        return $result;
    }
    
    /* Query Penelitian Berdasarkan ID */
    public function get_penelitian($penelitian_id)
    {
        $this->db->where('PENELITIAN_ID',$penelitian_id);
        $this->db->where('PENELITIAN_AKTIF',1);
        $query = $this->db->get('penelitian');
        return $query;
    }
    
    /* Query Penelitian berdasarkan User */
    public function get_penelitian_by_user($userid)
    {
        $this->db->where('peneliti_id', $userid);
        $query = $this->db->get('anggota');
        $arr = array();
        if($query->num_rows() > 0)
        {
            foreach($query->result_array() as $r)
            {
                array_push($arr, $r['penelitian_id']);
            }
        }
        $query2 = $this->db->where_in('penelitian_id',$arr);
        return $query2;
    }
    
    /* Menghapus Penelitian yang dibuat */
    public function delete_penelitian($penelitian_id)
    {
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $this->db->where('PENELITIAN_AKTIF',1);
        $tmp1 = $this->db->get('penelitian');
        if($tmp1->num_rows() <= 0)
        {
            return FALSE;
        }
        
        $data = array(
          'PENELITIAN_STATUS' => 0  
        );
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $result = $this->db->update('penelitian',$data);
        return $result;
        
    }
    
    /* Melakukan upload proposal daftar - laporan PJ */
    public function upload_proposal_daftar($penelitian_id,$file_proposal_daftar)
    {
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $this->db->where('PENELITIAN_AKTIF',1);
        $tmp1 = $this->db->get('penelitian');
        if($tmp1->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('PENELITIAN_ID',$penelitian_id);
        $data = array(
            'PENELITIAN_FILE_PROPOSAL_DAFTAR' => $file_proposal_daftar,
            'PENELITIAN_STATUS_PROPOSAL_DAFTAR' => 1,
            'PENELITIAN_TANGGAL_UPLOAD_PROPOSAL_DAFTAR' => date("Y-m-d H:i:s")
        );
        $this->db->update('penelitian',$data);
    }
    
    public function upload_proposal_revisi($penelitian_id,$file_proposal_revisi)
    {
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $this->db->where('PENELITIAN_AKTIF',1);
        $tmp1 = $this->db->get('penelitian');
        if($tmp1->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('PENELITIAN_ID',$penelitian_id);
        $data = array(
            'PENELITIAN_FILE_PROPOSAL_REVISI' => $file_proposal_revisi,
            'PENELITIAN_STATUS_PROPOSAL_REVISI' => 1,
            'PENELITIAN_TANGGAL_UPLOAD_PROPOSAL_REVISI' => date("Y-m-d H:i:s")
        );
        $this->db->update('penelitian',$data);
    }

    public function upload_laporan_kemajuan($penelitian_id,$file_laporan_kemajuan)
    {
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $this->db->where('PENELITIAN_AKTIF',1);
        $tmp1 = $this->db->get('penelitian');
        if($tmp1->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('PENELITIAN_ID',$penelitian_id);
        $data = array(
            'PENELITIAN_FILE_LAPORAN_KEMAJUAN' => $file_laporan_kemajuan,
            'PENELITIAN_STATUS_LAPORAN_KEMAJUAN' => 1,
            'PENELITIAN_TANGGAL_UPLOAD_LAPORAN_KEMAJUAN' => date("Y-m-d H:i:s")
        );
        $this->db->update('penelitian',$data);
     }

    public function upload_laporan_akhir($penelitian_id,$file_laporan_akhir)
    {
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $this->db->where('PENELITIAN_AKTIF',1);
        $tmp1 = $this->db->get('penelitian');
        if($tmp1->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('PENELITIAN_ID',$penelitian_id);
        $data = array(
            'PENELITIAN_FILE_LAPORAN_AKHIR' => $file_laporan_akhir,
            'PENELITIAN_STATUS_LAPORAN_AKHIR' => 1,
            'PENELITIAN_TANGGAL_UPLOAD_LAPORAN_AKHIR' => date("Y-m-d H:i:s"),
        );
        $this->db->update('penelitian',$data);
    }
    
    public function upload_laporan_pj($penelitian_id,$file_laporan_pj)
    {
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $this->db->where('PENELITIAN_AKTIF',1);
        $tmp1 = $this->db->get('penelitian');
        if($tmp1->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('PENELITIAN_ID',$penelitian_id);
        $data = array(
            'PENELITIAN_FILE_LAPORAN_PJ' => $file_laporan_pj,
            'PENELITIAN_STATUS_LAPORAN_PJ' => 1,
            'PENELITIAN_TANGGAL_UPLOAD_LAPORAN_PJ' => date("Y-m-d H:i:s"),
        );
        $this->db->update('penelitian',$data);
    }

    public function upload_surat_perjanjian($penelitian_id,$file_surat_perjanjian)
    {
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $this->db->where('PENELITIAN_AKTIF',1);
        $tmp1 = $this->db->get('penelitian');
        if($tmp1->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('PENELITIAN_ID',$penelitian_id);
        $data = array(
            'PENELITIAN_FILE_SURAT_PERJANJIAN' => $file_surat_perjanjian,
            'PENELITIAN_STATUS_SURAT_PERJANJIAN' => 1,
            'PENELITIAN_TANGGAL_UPLOAD_SURAT_PERJANJIAN' => date("Y-m-d H:i:s"),
        );
        $this->db->update('penelitian',$data);        
    }

    public function upload_sk_selesai($penelitian_id,$file_sk_selesai)
    {
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $this->db->where('PENELITIAN_AKTIF',1);
        $tmp1 = $this->db->get('penelitian');
        if($tmp1->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('PENELITIAN_ID',$penelitian_id);
        $data = array(
            'PENELITIAN_FILE_SK_SELESAI' => $file_sk_selesai,
            'PENELITIAN_STATUS_SK_SELESAI' => 1,
            'PENELITIAN_TANGGAL_UPLOAD_SK_SELESAI' => date("Y-m-d H:i:s"),
        );
        $this->db->update('penelitian',$data);   
    }
    
    /* Fungsi admin untuk menyetujui penelitian */
    public function response_accepted($penelitian_id,$admin_id, $jum_dana)
    {
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $this->db->where('PENELITIAN_AKTIF',1);
        $tmp1 = $this->db->get('penelitian');
        if($tmp1->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('ADMIN_ID', $admin_id);
        $tmp2 = $this->db->get('admin');
        if($tmp2->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('PENELITIAN_ID',$penelitian_id);
        $data = array(
            'ADMIN_ID' => $admin_id,
            'PENELITIAN_JUM_DANA_DISETUJUI' => $jum_dana,
            'PENELITIAN_TANGGAL_DISETUJUI' => date("Y-m-d H:i:s"),
            'STATUSPEN_ID' => $STATUS_DISETUJUI
        );
        $this->db->update('penelitian',$data);        
    }

    /* Fungsi admin untuk menolak penelitian */
    public function response_rejected($penelitian_id,$admin_id)
    {
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $this->db->where('PENELITIAN_AKTIF',1);
        $tmp1 = $this->db->get('penelitian');
        if($tmp1->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('ADMIN_ID', $admin_id);
        $tmp2 = $this->db->get('admin');
        if($tmp2->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('PENELITIAN_ID',$penelitian_id);
        $data = array(
            'ADMIN_ID' => $admin_id,
            'STATUSPEN_ID' => $STATUS_DITOLAK
        );
        $this->db->update('penelitian',$data);        
    }
    
    /* yang bisa menyelesaikan penelitian siapa?? */
    public function response_finished($penelitian_id,$admin_id)
    {
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $this->db->where('PENELITIAN_AKTIF',1);
        $tmp1 = $this->db->get('penelitian');
        if($tmp1->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('ADMIN_ID', $admin_id);
        $tmp2 = $this->db->get('admin');
        if($tmp2->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('PENELITIAN_ID',$penelitian_id);
        $data = array(
            'ADMIN_ID' => $admin_id,
            'STATUSPEN_ID' => $STATUS_SELESAI,
            'PENELITIAN_TANGGAL_SELESAI' => date("Y-m-d H:i:s"),
        );
        $this->db->update('penelitian',$data);        
    }  
    
    /* Menambah anggota */
    public function insert_anggota($penelitian_id,$peneliti_id,$sebagai)
    {
//        $this->db->where('PENELITIAN_ID', $penelitian_id);
//        $this->db->where('PENELITIAN_AKTIF',1);
//        $tmp1 = $this->db->get('penelitian');
//        if($tmp1->num_rows() <= 0)
//        {
//            return FALSE;
//        }
//        $this->db->where('PENELITI_ID', $peneliti_id);
//        $tmp2 = $this->db->get('peneliti');
//        if($tmp2->num_rows() <= 0)
//        {
//            return FALSE;
//        }
//        $this->db->where('PENELITIAN_ID', $penelitian_id);
//        $this->db->where('PENELITI_ID', $peneliti_id);
//        $tmp3 = $this->db->get('anggota');
//        if($tmp3->num_rows() <= 0)
//        {
//            return FALSE;
//        }
        $data = array(
            'PENELITIAN_ID' => $penelitian_id,
            'PENELITI_ID' => $peneliti_id,
            'ANGGOTA_SEBAGAI' => $sebagai
        );
        $result = $this->db->insert('anggota',$data);
        return $result;
    }
    
    /* Menghapus anggota dari penelitian */
    public function delete_anggota($penelitian_id,$peneliti_id)
    {
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $this->db->where('PENELITIAN_AKTIF',1);
        $tmp1 = $this->db->get('penelitian');
        if($tmp1->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('PENELITI_ID', $peneliti_id);
        $tmp2 = $this->db->get('peneliti');
        if($tmp2->num_rows() <= 0)
        {
            return FALSE;
        }
        $this->db->where('PENELITIAN_ID', $penelitian_id);
        $this->db->where('PENELITI_ID', $peneliti_id);
        $this->db->where('ANGGOTA_SEBAGAI', 2);
        $tmp3 = $this->db->delete('anggota');        
    }
    
    public function get_penelitian_by_peneliti($peneliti_id){
        $query = "SELECT *
        FROM peneliti p, anggota a, penelitian pl, status_penelitian sp
        WHERE p.peneliti_id = a.peneliti_id and a.penelitian_id = pl.penelitian_id and pl.statuspen_id = sp.statuspen_id and p.peneliti_id  = $peneliti_id";
        return $this->db->query($query);
    }
    
    public function get_proposal_baru(){
            $query ="select * from skim s, penelitian p, anggota a, status_penelitian sp, peneliti pl
            where s.skim_id = p.skim_id and p.statuspen_id = sp.statuspen_id and a.penelitian_id = p.penelitian_id 
            and a.peneliti_id = pl.peneliti_id and  sp.statuspen_id = 1 and a.anggota_sebagai = 1 group by s.skim_id";
            return $this->db->query($query);
    }
    
    


    public function get_timpenelitian($penelitian_id){
        $query = "SELECT *
        FROM peneliti p, anggota a, penelitian pl
        WHERE p.peneliti_id = a.peneliti_id and a.penelitian_id = pl.penelitian_id and pl.penelitian_id  = $penelitian_id";
        return $this->db->query($query);
    }
    
    public function get_penelitian_by_skim($skim_id)
    {
        $this->db->where('SKIM_ID',$skim_id);
        return $this->db->get('penelitian');
    }
    
    public function get_penelitian_by_id($penelitian_id)
    {
        $this->db->where('PENELITIAN_ID',$penelitian_id);
        return $this->db->get('penelitian');
    }
    
} ?>